import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import net.sf.json.*;

public class DBHandler {
	// Database info
	private static final String HOST_NAME = "foldit-db01.cs.washington.edu:3306";
	private static final String DB_NAME = "cgs_gc_simeco";
	private static final String USER_NAME = "gc_sime_adm";
	private static final String PASSWORD = "t39xR30g5c";

	private Connection conn;

	public DBHandler() {
		try {
			conn = DriverManager.getConnection("jdbc:mysql://" + HOST_NAME + "/"
					+ DB_NAME + "?user=" + USER_NAME + "&password=" + PASSWORD);
			conn.setAutoCommit(false);
			conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

			prepareStatements();
		} catch (SQLException e) {
			System.out.println("Exception while connecting to database: " + e);
		}
	}

	private void prepareStatements() {
		try {
			getPlayer = conn.prepareStatement(getPlayerStr, PreparedStatement.RETURN_GENERATED_KEYS);
			getQuests = conn.prepareStatement(getQuestsStr, PreparedStatement.RETURN_GENERATED_KEYS);
			getInventoryAt = conn.prepareStatement(getInventoryAtStr, PreparedStatement.RETURN_GENERATED_KEYS);
			getShips = conn.prepareStatement(getShipsStr, PreparedStatement.RETURN_GENERATED_KEYS);
			getShipEquipment = conn.prepareStatement(getShipEquipmentStr, PreparedStatement.RETURN_GENERATED_KEYS);
			getBuildings = conn.prepareStatement(getBuildingsStr, PreparedStatement.RETURN_GENERATED_KEYS);
			getPlayerItemsForSale = conn.prepareStatement(getPlayerItemsForSaleStr, PreparedStatement.RETURN_GENERATED_KEYS);
			getPlayerItemsForSaleAt = conn.prepareStatement(getPlayerItemsForSaleAtStr, PreparedStatement.RETURN_GENERATED_KEYS);
			getItemsForSaleAt = conn.prepareStatement(getItemsForSaleAtStr, PreparedStatement.RETURN_GENERATED_KEYS);
			getCrew = conn.prepareStatement(getCrewStr, PreparedStatement.RETURN_GENERATED_KEYS);
			getCrewAt = conn.prepareStatement(getCrewAtStr, PreparedStatement.RETURN_GENERATED_KEYS);
			addPlayer = conn.prepareStatement(addPlayerStr, PreparedStatement.RETURN_GENERATED_KEYS);
			changePlayerLoc = conn.prepareStatement(changePlayerLocStr, PreparedStatement.RETURN_GENERATED_KEYS);
			updateTutorialProgress = conn.prepareStatement(updateTutorialProgressStr, PreparedStatement.RETURN_GENERATED_KEYS);
			takeRequired = conn.prepareStatement(takeRequiredStr, PreparedStatement.RETURN_GENERATED_KEYS);
			removeQuest = conn.prepareStatement(removeQuestStr, PreparedStatement.RETURN_GENERATED_KEYS);
			assignQuest = conn.prepareStatement(assignQuestStr, PreparedStatement.RETURN_GENERATED_KEYS);
			updateQuestMinLeft = conn.prepareStatement(updateQuestMinLeftStr, PreparedStatement.RETURN_GENERATED_KEYS);
			updateQuestContribution = conn.prepareStatement(updateQuestContributionStr, PreparedStatement.RETURN_GENERATED_KEYS);
			clearInventory = conn.prepareStatement(clearInventoryStr, PreparedStatement.RETURN_GENERATED_KEYS);
			updateItemSale = conn.prepareStatement(updateItemSaleStr, PreparedStatement.RETURN_GENERATED_KEYS);
			removeSoldItem = conn.prepareStatement(removeSoldItemStr, PreparedStatement.RETURN_GENERATED_KEYS);
			updateGold = conn.prepareStatement(updateGoldStr, PreparedStatement.RETURN_GENERATED_KEYS);
			addShip = conn.prepareStatement(addShipStr, PreparedStatement.RETURN_GENERATED_KEYS);
			deleteShip = conn.prepareStatement(deleteShipStr, PreparedStatement.RETURN_GENERATED_KEYS);
			changeActiveShip = conn.prepareStatement(changeActiveShipStr, PreparedStatement.RETURN_GENERATED_KEYS);
			equipItem = conn.prepareStatement(equipItemStr, PreparedStatement.RETURN_GENERATED_KEYS);
			constructBuilding = conn.prepareStatement(constructBuildingStr, PreparedStatement.RETURN_GENERATED_KEYS);
			deleteBuilding = conn.prepareStatement(deleteBuildingStr, PreparedStatement.RETURN_GENERATED_KEYS);
			addCrewToShip = conn.prepareStatement(addCrewToShipStr, PreparedStatement.RETURN_GENERATED_KEYS);
			clearCrew = conn.prepareStatement(clearCrewStr, PreparedStatement.RETURN_GENERATED_KEYS);
			giveReward = conn.prepareStatement(giveRewardStr, PreparedStatement.RETURN_GENERATED_KEYS);
			addItem = conn.prepareStatement(addItemStr, PreparedStatement.RETURN_GENERATED_KEYS);
			moveItem = conn.prepareStatement(moveItemStr, PreparedStatement.RETURN_GENERATED_KEYS);
			updateQty = conn.prepareStatement(updateQtyStr, PreparedStatement.RETURN_GENERATED_KEYS);
			dropItem = conn.prepareStatement(dropItemStr, PreparedStatement.RETURN_GENERATED_KEYS);
			subtractQty = conn.prepareStatement(subtractQtyStr, PreparedStatement.RETURN_GENERATED_KEYS);
			subtractGold = conn.prepareStatement(subtractGoldStr, PreparedStatement.RETURN_GENERATED_KEYS);
			addGold = conn.prepareStatement(addGoldStr, PreparedStatement.RETURN_GENERATED_KEYS);
			addBoughtItem = conn.prepareStatement(addBoughtItemStr, PreparedStatement.RETURN_GENERATED_KEYS);
			setAllShipsInactive = conn.prepareStatement(setAllShipsInactiveStr, PreparedStatement.RETURN_GENERATED_KEYS);
			changeShipStats = conn.prepareStatement(changeShipStatsStr, PreparedStatement.RETURN_GENERATED_KEYS);
			addCrewToNew = conn.prepareStatement(addCrewToNewStr, PreparedStatement.RETURN_GENERATED_KEYS);
			subtractCrewFromOld = conn.prepareStatement(subtractCrewFromOldStr, PreparedStatement.RETURN_GENERATED_KEYS);
			updatePlayerXp = conn.prepareStatement(updatePlayerXpStr, PreparedStatement.RETURN_GENERATED_KEYS);
		} catch (SQLException e) {
			System.out.println("Exception while preparing statements: " + e);
		}
	}
	
	// TODO: Connection pooling to handle high load.

	/**
	 * ALL QUERIES
	 */

	// GetPlayer(email)
	private PreparedStatement getPlayer;
	private static final String getPlayerStr = "select * from st_Player where email=?";
	private static final String[] playerReturns = { "id", "xp", "locId", "gold", "tutorialProgress" };

	public JSONObject getPlayer(String email) {
		try {
			getPlayer.setString(1, email);
			ResultSet results = getPlayer.executeQuery();
			conn.commit();
			
			return resultsToJson(results, playerReturns);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("getPlayer", e);
		}
	}

	// GetQuest(pid)
	private PreparedStatement getQuests;
	private static final String getQuestsStr = "select * from st_Quest where pid=?";
	private static final String[] questReturns = { "id", "minLeft", "contribution",
			"locId", "templateId", "fromId", "reqItemId", "reqItemQty", "rewItemId",
			"rewItemQty" };

	public JSONObject getQuests(int pid) {
		try {
			getQuests.setInt(1, pid);
			ResultSet results = getQuests.executeQuery();
			conn.commit();

			return resultsToJson(results, questReturns);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("getQuest", e);
		}
	}

	// GetInventory(pid)
	private PreparedStatement getInventoryAt;
	private static final String getInventoryAtStr = "select * from st_Inventory where pid=? and locId=?";
	private static final String[] inventoryReturns = { "id", "pid", "itemId", "qty",
			"saleQty", "salePrice" };

	public JSONObject getInventoryAt(int pid, int locId) {
		try {
			getInventoryAt.setInt(1, pid);
			getInventoryAt.setInt(2, locId);
			ResultSet results = getInventoryAt.executeQuery();
			conn.commit();

			return resultsToJson(results, inventoryReturns);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("getInventoryAt", e);
		}
	}

	// GetShips(pid)
	private PreparedStatement getShips;
	private static final String getShipsStr = "select * from st_Ship where pid=?";
	private static final String[] shipReturns = {"id", "shipTypeId", "speed", "defense", "navigation",
			"slots", "capacity", "maintenance", "fuelEfficiency", "consRes1", "consQty1", "consRes2",
			"consQty2"};

	public JSONObject getShips(int pid) {
		try {
			getShips.setInt(1, pid);
			ResultSet results = getShips.executeQuery();
			conn.commit();

			return resultsToJson(results, shipReturns, "active");
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("geShips", e);
		}
	}

	// GetShipEquipment(shipId)
	private PreparedStatement getShipEquipment;
	private static final String getShipEquipmentStr = "select * from st_ShipEquipment where shipId=?";
	private static final String[] shipEquipmentReturns = {"slotId", "itemId"};

	public JSONObject getShipEquipment(int shipId) {
		try {
			getShipEquipment.setInt(1, shipId);
			ResultSet results = getShipEquipment.executeQuery();
			conn.commit();

			return resultsToJson(results, shipEquipmentReturns);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("getShipEquipment", e);
		}
	}

	// GetBuildings(pid, locId)
	private PreparedStatement getBuildings;
	private static final String getBuildingsStr = "select * from st_Building where pid=? and locId=?";
	private static final String[] buildingReturns = {"id", "bldgTypeId"};
	
	public JSONObject getBuildings(int pid, int locId) {
		try {
			getBuildings.setInt(1, pid);
			getBuildings.setInt(2, locId);
			ResultSet results = getBuildings.executeQuery();
			conn.commit();

			return resultsToJson(results, buildingReturns);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("getBuildings", e);
		}
	}

	// GetPlayerItemsForSale(pid)
	private PreparedStatement getPlayerItemsForSale;
	private static final String getPlayerItemsForSaleStr = "select * from st_Inventory where pid=? and saleQty>0";

	public JSONObject getPlayerItemsForSale(int pid) {
		try {
			getPlayerItemsForSale.setInt(1, pid);
			ResultSet results = getPlayerItemsForSale.executeQuery();
			conn.commit();

			return resultsToJson(results, inventoryReturns);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("getPlayerItemsForSale", e);
		}
	}

	// GetPlayerItemsForSaleAt(pid, locId)
	private PreparedStatement getPlayerItemsForSaleAt;
	private static final String getPlayerItemsForSaleAtStr = "select * from st_Inventory where pid=? and saleQty>0 and locId=?";

	public JSONObject getPlayerItemsForSaleAt(int pid, int locId) {
		try {
			getPlayerItemsForSaleAt.setInt(1, pid);
			getPlayerItemsForSaleAt.setInt(2, locId);
			ResultSet results = getPlayerItemsForSaleAt.executeQuery();
			conn.commit();

			return resultsToJson(results, inventoryReturns);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("getPlayerItemsForSaleAt", e);
		}
	}

	// GetItemsForSaleAt(locId)
	private PreparedStatement getItemsForSaleAt;
	private static final String getItemsForSaleAtStr = "select * from st_Inventory where saleQty>0 and locId=?";

	public JSONObject getItemsForSaleAt(int locId) {
		try {
			getItemsForSaleAt.setInt(1, locId);
			ResultSet results = getItemsForSaleAt.executeQuery();
			conn.commit();
			
			return resultsToJson(results, inventoryReturns);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("getItemsForSaleAt", e);
		}
	}

	// GetCrew(pid)
	private PreparedStatement getCrew;
	private static final String getCrewStr = "select * from st_Crew where pid=?";
	private static final String[] crewReturns = {"locId", "taskId", "qty"};

	public JSONObject getCrew(int pid) {
		try {
			getCrew.setInt(1, pid);
			ResultSet results = getCrew.executeQuery();
			conn.commit();
			
			return resultsToJson(results, crewReturns);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("getCrew", e);
		}
	}

	// GetCrewAt(pid, locId)
	private PreparedStatement getCrewAt;
	private static final String getCrewAtStr = "select * from st_Crew where pid=? and locId=?";

	public JSONObject getCrewAt(int pid, int locId) {
		try {
			getCrewAt.setInt(1, pid);
			getCrewAt.setInt(2, locId);
			ResultSet results = getCrewAt.executeQuery();
			conn.commit();
			
			return resultsToJson(results, crewReturns);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("getCrewAt", e);
		}
	}

	// AddPlayer(email)
	private PreparedStatement addPlayer;
	private static final String addPlayerStr = "insert into st_Player "
			+ "(email, locId) " + "values (?,?)";

	public JSONObject addPlayer(String email, int locId) {
		try {
			addPlayer.setString(1, email);
			addPlayer.setInt(2, locId);
			addPlayer.executeUpdate();
			conn.commit();
			
			return successJson(addPlayer.getGeneratedKeys());
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("addPlayer", e);
		}
	}
	
	// ChangePlayerLocation(pid, locId)
	private PreparedStatement changePlayerLoc;
	private static final String changePlayerLocStr = "update st_Player set locId=? where id=?";
	
	public JSONObject changePlayerLoc(int pid, int locId) {
		try {
			changePlayerLoc.setInt(1, locId);
			changePlayerLoc.setInt(2, pid);
			changePlayerLoc.executeUpdate();
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("changePlayerLoc", e);
		}
	}
	
	// UpdateTutorialProgress(pid, tutorialProgress)
	private PreparedStatement updateTutorialProgress;
	private static final String updateTutorialProgressStr = "update st_Player set tutorialProgress=? where id=?";
	
	public JSONObject updateTutorialProgress(int pid, int tutPro) {
		try {
			updateTutorialProgress.setInt(1, tutPro);
			updateTutorialProgress.setInt(2, pid);
			updateTutorialProgress.executeUpdate();
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("updateTutorialProgress", e);
		}
	}

	// EndQuest(id, completed?, pid, rewardToLocId, itemId, qty)
	private PreparedStatement giveReward;
	private static final String giveRewardStr = "insert into st_Inventory (pid, locId, itemId, qty) "
			+ "values (?,?,?,?) "
			+ "on duplicate key "
			+ "update qty=qty+?";
	private PreparedStatement takeRequired;
	private static final String takeRequiredStr = "update st_Inventory set qty=qty-? where id=?";
	private PreparedStatement removeQuest;
	private static final String removeQuestStr = "delete from st_Quest where id=?";

	public JSONObject endQuest(int id, boolean completed, int pid, int reqInvItemId, int reqItemQty,
			int rewardToLocId, int rewardItemId, int rewardQty) {
		try {
			if (completed) {
				giveReward.setInt(1, pid);
				giveReward.setInt(2, rewardToLocId);
				giveReward.setInt(3, rewardItemId);
				giveReward.setInt(4, rewardQty);
				giveReward.setInt(5, rewardQty);
				giveReward.executeUpdate();
				
				takeRequired.setInt(1, reqItemQty);
				takeRequired.setInt(2, reqInvItemId);
				int affectedRows = takeRequired.executeUpdate();
				if (affectedRows == 0) {
					conn.rollback();
					return errorJson(2, "You no longer own this item.");
				}
				
				clearInventory.executeUpdate();
			}
			
			removeQuest.setInt(1, id);
			removeQuest.executeUpdate();
			conn.commit();
			
			return successJson(giveReward.getGeneratedKeys());
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("endQuest", e);
		}
	}

	// AssignQuest(pid, minLeft, templateId, fromId, reqItemId, reqItemQty,
	// rewItemId, rewItemQty)
	private PreparedStatement assignQuest;
	private static final String assignQuestStr = "insert into st_Quest "
			+ "(pid, minLeft, templateId, fromId, locId, reqItemId, reqItemQty, rewItemId, rewItemQty) "
			+ "values (?, ?, ?, ?, ?, ?, ?, ?, ?)";

	public JSONObject assignQuest(int pid, int minLeft, int templateId, int fromId, int locId,
			int reqItemId, int reqItemQty, int rewItemId, int rewItemQty) {
		try {
			assignQuest.setInt(1, pid);
			assignQuest.setInt(2, minLeft);
			assignQuest.setInt(3, templateId);
			assignQuest.setInt(4, fromId);
			assignQuest.setInt(5, locId);
			assignQuest.setInt(6, reqItemId);
			assignQuest.setInt(7, reqItemQty);
			assignQuest.setInt(8, rewItemId);
			assignQuest.setInt(9, rewItemQty);
			assignQuest.executeUpdate();
			conn.commit();
			
			return successJson(assignQuest.getGeneratedKeys());
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("assignQuest", e);
		}
	}

	// UpdateQuestMinLeft(id, newMinLeft)
	private PreparedStatement updateQuestMinLeft;
	private static final String updateQuestMinLeftStr = "update st_Quest set minLeft=? where id=?";

	public JSONObject updateQuestMinLeft(int id, int newMinLeft) {
		try {
			updateQuestMinLeft.setInt(1, newMinLeft);
			updateQuestMinLeft.setInt(2, id);
			updateQuestMinLeft.executeUpdate();
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("updateQuestMinLeft", e);
		}
	}

	// UpdateQuestContribution(id, newContributionAmt)
	private PreparedStatement updateQuestContribution;
	private static final String updateQuestContributionStr = "update st_Quest set contribution=? where id=?";

	public JSONObject updateQuestContribution(int id, int newContribution) {
		try {
			updateQuestContribution.setInt(1, newContribution);
			updateQuestContribution.setInt(2, id);
			updateQuestContribution.executeUpdate();
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("updateQuestContribution", e);
		}
	}
	
	// AddItem(pid, itemId, locId, qty)
	private PreparedStatement addItem;
	private static final String addItemStr = "insert into st_Inventory (pid, locId, itemId, qty) "
			+ "values (?,?,?,?) on duplicate key update qty=qty+?";
	
	public JSONObject addItem(int pid, int itemId, int locId, int qty) {
		try {
			addItem.setInt(1, pid);
			addItem.setInt(2, locId);
			addItem.setInt(3, itemId);
			addItem.setInt(4, qty);
			addItem.setInt(5, qty);
			addItem.executeUpdate();
			conn.commit();
			
			return successJson(addItem.getGeneratedKeys());
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("addItem", e);
		}
	}
	
	private PreparedStatement updateQty;
	private static final String updateQtyStr = "update st_Inventory set qty=? where id=?";
	
	public JSONObject updateQty(int invItemId, int qty) {
		try {
			updateQty.setInt(1, qty);
			updateQty.setInt(2, invItemId);
			updateQty.executeUpdate();
			
			clearInventory.executeUpdate();
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("updateQty", e);
		}
	}
	
	// DropItem(invItemId)
	private PreparedStatement dropItem;
	private static final String dropItemStr = "delete st_Inventory where id=?";
	
	public JSONObject dropItem(int invItemId) {
		try {
			dropItem.setInt(1, invItemId);
			dropItem.executeUpdate();
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("dropItem", e);
		}
	}
	
	// MoveItem(invItemId, toLocId, qty)
	private PreparedStatement moveItem;
	private static final String moveItemStr = "insert into st_Inventory (pid, locId, itemId, qty) "
			+ "select pid, ?, itemId, ? from st_Inventory I where id=? "
			+ "on duplicate key update qty=I.qty+?";
	private PreparedStatement subtractQty;
	private static final String subtractQtyStr = "update st_Inventory set qty=qty-? where id=?";
	private PreparedStatement clearInventory;
	private static final String clearInventoryStr = "delete from st_Inventory where qty=0";

	public JSONObject moveItem(int invItemId, int toLocId, int qty) {
		try {
			// Should never get a DNE error here b/c updateSale should be called
			// first if currently on the market.
			moveItem.setInt(1, toLocId);
			moveItem.setInt(2, qty);
			moveItem.setInt(3, invItemId);
			moveItem.setInt(4, qty);
			moveItem.executeUpdate();
			
			subtractQty.setInt(1, qty);
			subtractQty.setInt(2, invItemId);
			subtractQty.executeUpdate();
			
			clearInventory.executeUpdate();
			conn.commit();
			
			return successJson(moveItem.getGeneratedKeys());
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("moveItem", e);
		}
	}

	// UpdateItemSale(invItemId, saleQty, salePrice)
	private PreparedStatement updateItemSale;
	private static final String updateItemSaleStr = "update st_Inventory set saleQty=?, salePrice=? where id=?";

	public JSONObject updateItemSale(int invItemId, int saleQty, int salePrice) {
		try {
			updateItemSale.setInt(1, saleQty);
			updateItemSale.setInt(2, salePrice);
			updateItemSale.setInt(3, invItemId);
			int affectedRows = updateItemSale.executeUpdate();
			if (affectedRows == 0) {
				conn.rollback();
				return errorJson(2, "You no longer own this item.");
			}
			
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("updateItemSale", e);
		}
	}

	// Buy(pid, invItemId, itemId, salePrice, qty, sellToLocId)
	private PreparedStatement removeSoldItem;
	private static final String removeSoldItemStr = "update st_Inventory set qty=qty-?, saleQty=saleQty-? "
			+ "where id=?";
	private PreparedStatement addBoughtItem;
	private static final String addBoughtItemStr = "insert into st_Inventory (pid, locId, itemId, qty) "
			+ "values (?, ?, ?, ?) "
			+ "on duplicate key update qty=qty+?";
	private PreparedStatement subtractGold;
	private static final String subtractGoldStr = "update st_Player set gold=gold-? where id=?";
	private PreparedStatement addGold;
	private static final String addGoldStr = "update st_Player set gold=gold+? where id=?";

	public JSONObject buy(int buyerPid, int sellerPid, int invItemId, int itemId, int salePrice, int qty, int sellToLocId) {
		try {
			removeSoldItem.setInt(1, qty);
			removeSoldItem.setInt(2, qty);
			removeSoldItem.setInt(3, invItemId);
			int rowsAffected = removeSoldItem.executeUpdate();
			if (rowsAffected == 0) {
				conn.rollback();
				return errorJson(1, "This item has already been bought");
			}
			clearInventory.executeUpdate();
			
			addBoughtItem.setInt(1, buyerPid);
			addBoughtItem.setInt(2, sellToLocId);
			addBoughtItem.setInt(3, itemId);
			addBoughtItem.setInt(4, qty);
			addBoughtItem.setInt(5, qty);
			addBoughtItem.executeUpdate();
			
			subtractGold.setInt(1, salePrice*qty);
			subtractGold.setInt(2, buyerPid);
			subtractGold.executeUpdate();
			
			addGold.setInt(1, salePrice*qty);
			addGold.setInt(2, sellerPid);
			addGold.executeUpdate();
			
			conn.commit();
			
			return successJson(addBoughtItem.getGeneratedKeys());
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("buy", e);
		}
	}

	// UpdateGold(pid, amt)
	private PreparedStatement updateGold;
	private static final String updateGoldStr = "update st_Player set gold=? where id=?";

	public JSONObject updateGold(int pid, int amt) {
		try {
			updateGold.setInt(1, amt);
			updateGold.setInt(2, pid);
			updateGold.executeUpdate();
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("updateGold", e);
		}
	}

	// AddShip(pid, shipTypeId, capacity, defense, consRes1, consQty1, consRes2,
	// consQty2)
	private PreparedStatement addShip;
	private static final String addShipStr = "insert into st_Ship "
			+ "(pid, shipTypeId, speed, defense, navigation, slots, capacity, maintenance, "
			+ "fuelEfficiency, consRes1, consQty1, consRes2, consQty2) "
			+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?)";

	public JSONObject addShip(int pid, int shipTypeId, int speed, int defense, int navigation, int slots,
			int capacity, int maintenance, int fuelEfficiency, int consRes1, int consQty1, int consRes2,
			int consQty2) {
		try {
			addShip.setInt(1, pid);
			addShip.setInt(2, shipTypeId);
			addShip.setInt(3, speed);
			addShip.setInt(4, defense);
			addShip.setInt(5, navigation);
			addShip.setInt(6, slots);
			addShip.setInt(7, capacity);
			addShip.setInt(8, fuelEfficiency);
			addShip.setInt(9, maintenance);
			addShip.setInt(10, consRes1);
			addShip.setInt(11, consQty1);
			addShip.setInt(12, consRes2);
			addShip.setInt(13, consQty2);
			addShip.executeUpdate();
			conn.commit();
			
			return successJson(addShip.getGeneratedKeys());
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("addShip", e);
		}
	}

	// DeleteShip(shipId)
	private PreparedStatement deleteShip;
	private static final String deleteShipStr = "delete from st_Ship where id=?";

	public JSONObject deleteShip(int shipId) {
		try {
			deleteShip.setInt(1, shipId);
			deleteShip.executeUpdate();
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("deleteShip", e);
		}
	}

	// ChangeActiveShipTo(pid, shipId)
	private PreparedStatement setAllShipsInactive;
	private static final String setAllShipsInactiveStr = "update st_Ship set active=false where pid=?";
	private PreparedStatement changeActiveShip;
	private static final String changeActiveShipStr = "update st_Ship set active=true where id=?";

	public JSONObject changeActiveShip(int pid, int shipId) {
		try {
			setAllShipsInactive.setInt(1, pid);
			setAllShipsInactive.executeUpdate();
			changeActiveShip.setInt(1, shipId);
			changeActiveShip.executeUpdate();
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("changeActiveShip", e);
		}
	}

	// EquipToShip(shipId, slotId, itemId, newCapacity, newDefense, newConsRes1,
	// newConsQty1, newConsRes2, newConsQty2)
	private PreparedStatement changeShipStats;
	private static final String changeShipStatsStr = "update st_Ship "
			+ "set capacity=?, defense=?, consRes1=?, consQty1=?, consRes2=?, consQty2=? "
			+ "where id=?";
	private PreparedStatement equipItem;
	private static final String equipItemStr = "insert into st_ShipEquipment (shipId, slotId, itemId) "
			+ "values (?,?,?) on duplicate key update itemId=?";

	public JSONObject equipToShip(int shipId, int slotId, int itemId, int capacity,
			int defense, int consRes1, int consQty1, int consRes2,
			int consQty2) {
		try {
			changeShipStats.setInt(1, capacity);
			changeShipStats.setInt(2, defense);
			changeShipStats.setInt(3, consRes1);
			changeShipStats.setInt(4, consQty1);
			changeShipStats.setInt(5, consRes2);
			changeShipStats.setInt(6, consQty2);
			changeShipStats.setInt(7, shipId);
			changeShipStats.executeUpdate();
			
			equipItem.setInt(1, shipId);
			equipItem.setInt(2, slotId);
			equipItem.setInt(3, itemId);
			equipItem.setInt(4, itemId);
			equipItem.executeUpdate();
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("equipToShip", e);
		}
	}

	// ConstructBuilding(pid, locId, bldgTypeId)
	private PreparedStatement constructBuilding;
	private static final String constructBuildingStr = "insert into st_Building (pid, locId, bldgTypeId) "
			+ "values (?,?,?)";

	public JSONObject constructBuilding(int pid, int locId, int bldgTypeId) {
		try {
			constructBuilding.setInt(1, pid);
			constructBuilding.setInt(2, locId);
			constructBuilding.setInt(3, bldgTypeId);
			constructBuilding.executeUpdate();
			conn.commit();
			
			return successJson(constructBuilding.getGeneratedKeys());
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("constructBuilding", e);
		}
	}

	// SellBuilding(bldgId)
	private PreparedStatement deleteBuilding;
	private static final String deleteBuildingStr = "delete from st_Building where id=?";

	public JSONObject deleteBuilding(int bldgId) {
		try {
			deleteBuilding.setInt(1, bldgId);
			deleteBuilding.executeUpdate();
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("deleteBuilding", e);
		}
	}

	// AddCrew(pid, shipLocId, onShipTaskId)
	private PreparedStatement addCrewToShip;
	private static final String addCrewToShipStr = "insert into st_Crew (pid, locId, taskId, qty) "
			+ "values (?,?,?,1) "
			+ "on duplicate key update qty=qty+1";

	public JSONObject addCrewToShip(int pid, int shipLocId, int onShipTaskId) {
		try {
			addCrewToShip.setInt(1, pid);
			addCrewToShip.setInt(2, shipLocId);
			addCrewToShip.setInt(3, onShipTaskId);
			addCrewToShip.executeUpdate();
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("addCrewToShip", e);
		}
	}

	// UpdateCrew(pid, oldLocId, oldTaskId, newLocId, newTaskId, qty)
	private PreparedStatement addCrewToNew;
	private static final String addCrewToNewStr = "insert into st_Crew (pid, locId, taskId, qty) "
			+ "values (?,?,?,?) "
			+ "on duplicate key update qty=qty+?";
	private PreparedStatement subtractCrewFromOld;
	private static final String subtractCrewFromOldStr = "update st_Crew set qty=qty-? where pid=? and locId=? and taskId=?";
	private PreparedStatement clearCrew;
	private static final String clearCrewStr = "delete from st_Crew where qty=0";

	public JSONObject moveCrew(int pid, int oldLocId, int oldTaskId,
			int newLocId, int newTaskId, int qty) {
		try {
			addCrewToNew.setInt(1, pid);
			addCrewToNew.setInt(2, newLocId);
			addCrewToNew.setInt(3, newTaskId);
			addCrewToNew.setInt(4, qty);
			addCrewToNew.setInt(5, qty);
			addCrewToNew.executeUpdate();
			
			subtractCrewFromOld.setInt(1, qty);
			subtractCrewFromOld.setInt(2, pid);
			subtractCrewFromOld.setInt(3, oldLocId);
			subtractCrewFromOld.setInt(4, oldTaskId);
			subtractCrewFromOld.executeUpdate();
			
			clearCrew.executeUpdate();
			conn.commit();
			
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("clearCrew", e);
		}
	}

	/**
	 * Updates a player's experience. Updated internally based on the user's
	 * actions.
	 */

	private PreparedStatement updatePlayerXp;
	private static final String updatePlayerXpStr = "update st_Player set xp=? where id=?";

	public JSONObject updatePlayerXp(int id, int experience) {
		try {
			updatePlayerXp.setInt(1, experience);
			updatePlayerXp.setInt(2, id);
			updatePlayerXp.executeUpdate();
			conn.commit();
			return successJson(null);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				return cannotRollbackException(e, ex);
			}
			return sqlException("updatePlayerXp", e);
		}
	}

	// ======================================================
	// PROTECTED METHODS TO CLEAR ROWS FROM TABLES
	// ======================================================
	
	protected void deletePlayer(int id) {
		try {
			PreparedStatement dp = conn.prepareStatement("delete from Player where id=?");
			dp.setInt(1, id);
			dp.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			
		}
	}
	
	protected void deleteQuest(int id) {
		try {
			removeQuest.setInt(1, id);
			removeQuest.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			
		}
	}
	
	protected void deleteInventoryItem(int id) {
		dropItem(id);
	}
	
	// deleteShip already exists
	
	protected void deleteShipEquipment(int shipId, int slotId) {
		try {
			PreparedStatement dse = conn.prepareStatement("delete from ShipEquipment " +
					"where shipId=? and slotId=?");
			dse.setInt(1, shipId);
			dse.setInt(2, slotId);
			dse.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			
		}
	}
	
	// deleteBuilding already exists
	
	protected void deleteCrewGroup(int pid, int locId, int taskId) {
		try {
			PreparedStatement dcg = conn.prepareStatement("delete from ShipEquipment " +
					"where pid=? and locId=? and taskId=?");
			dcg.setInt(1, pid);
			dcg.setInt(2, locId);
			dcg.setInt(3, taskId);
			dcg.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			
		}
	}
	
	// ======================================================
	// BUILDING JSON
	// ======================================================
	
	private JSONObject resultsToJson(ResultSet results, String[] fields, 
			String... booleanFields) throws SQLException {
		JSONArray list = new JSONArray();
		for (int i = 0; results.next(); i++) {
			JSONObject innerJson = new JSONObject();
			for (String field : fields) {
				innerJson.put(field, results.getInt(field));
			}
			for (String field : booleanFields) {
				innerJson.put(field, results.getBoolean(field));
			}
			
			list.add(innerJson);
		}

		JSONObject json = new JSONObject();
		json.put("result", 0);
		json.put("details", list);
		return json;
	}
	
	private JSONObject successJson(ResultSet r /* for inserts */) throws SQLException {
		JSONObject json = new JSONObject();
		json.put("result", 0);
		if (r != null && r.first())
			json.put("id", r.getInt(1));
		return json;
	}
	
	private JSONObject sqlException(String function, SQLException e) {
		return errorJson(20, "Encountered exception in " + function + ": " + e);
	}
	
	private JSONObject cannotRollbackException(SQLException firstEx, SQLException rollbackEx) {
		return errorJson(21, "Unable to roll back after encountering exception: " + firstEx
				+ " due to another exception: " + rollbackEx);
	}
	
	private JSONObject errorJson(int errorCode, String error) {
		JSONObject json = new JSONObject();
		json.put("result", errorCode);
		json.put("details", error);
		return json;
	}
}
